import DB.DB;

import javax.swing.*;
import javax.swing.border.LineBorder;
import javax.swing.table.DefaultTableCellRenderer;
import java.awt.*;
import java.awt.event.*;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;

public class ReaderList extends JFrame {
    /*
    * SQL语句字符串
    * */
    private static final String SWI_OFF = "select * from book";
    private static final String SWI_ON = "select * from book where IsBorrowed = '否' ";
    private static final String SEARCH_BY_KEYVALUE = "select * from book where BookName like";
    private static final String SEARCH_BY_TYPE = "select * from book where BookType = ";
    private static final String SEARCH_BY_PUBLISHER = "select * from book where Publisher = ";
    private static final String SEARCH_BY_PUBLICDATE = "select * from book where PublicDate like ";
    private static final String SEARCH_ALL_NUMBER = "select count(*) '库存量' from book where BookName = ";
    private static final String SEARCH_BORROW_NUMBER = "select count(*) '借出量' from book where IsBorrowed = '是' and BookName = ";
    private static final String SEARCH_LEFT_NUMBER = "select count(*) '剩余量' from book where IsBorrowed = '否' and BookName = ";

    private boolean swi_on = false;
    private ArrayList<bookEntity> v = new ArrayList<>();
    private String bookImageURL;
    private String storedNum,borrowedNum,leftNum;

    private MyTableModel tableModel;
    private JTable table;
    private JScrollPane scroll;
    private JLabel welcome,exit,search,borrow,swi,select1,select2,select3;//定义标签（作用为按钮）
    private JLabel keyQueryLabel,typeQueryLabel,publisherQueryLabel,publicDateQueryLabel;//定义四种不同的查找方式的背景字体
    private JTextField user;//定义用户名
    private JTextField keyQuery,typeQuery,publisherQuery,publicDateQuery;//定义四种不同的查找方式
    private JTextField bookName,position,writer,publisherName,publicTime;//定义 书本基本信息
    private JTextField storedNumText,borrowedNumText,leftNumText;    //定义 库存量，借出量，剩余量
    private JLabel bookImage;

    public ReaderList(){

        super("图书管理系统");
        this.setSize(1920,1080);
        this.setLocationRelativeTo(null);
        this.setDefaultCloseOperation(EXIT_ON_CLOSE);
        ImageIcon img = new ImageIcon("images/图书管理系统.png");//这是背景图片
        JLabel imgLabel = new JLabel(img);//将背景图放在标签里。
        this.getLayeredPane().add(imgLabel, new Integer(Integer.MIN_VALUE));//背景标签
        imgLabel.setBounds(0,0,img.getIconWidth(), img.getIconHeight());//设置背景标签的位置
        Container cp=this.getContentPane();
        cp.setLayout(null);
        ((JPanel)cp).setOpaque(false); //注意这里，将内容面板设为透明

        //执行SWI_OFF语句来获得一个表格模型
        tableModel = SQLOperation(SWI_OFF);
        //获得一个表格对象
        table = new JTable(tableModel){
            public boolean isCellEditable(int row, int column) {
                return false;
            }//表格不允许被编辑
        };
        beautyTable();//美化表格
        scroll = new JScrollPane(table);
        scroll.setBounds(590, 300, 730, 513);
        scroll.setBorder(new LineBorder(null,0));
        scroll.getVerticalScrollBar().setUI(new DemoScrollBarUI());//重写方法★
        cp.add(scroll);

        user = new JTextField("虞瑞桥");
        user.setBounds(1450,15,100,40);
        beautifyTextField(user);
        user.setFont(new Font("黑体", Font.PLAIN, 19));
        user.setForeground(Color.white);
        user.setEditable(false);
        cp.add(user);

        welcome = new JLabel(new ImageIcon("images/欢迎.png"));
        welcome.setBounds(1550,15,40,40);
        cp.add(welcome);

        exit = new JLabel(new ImageIcon("images/退出登录.png"));
        exit.setBounds(1672,15,100,40);
        cp.add(exit);

        search = new JLabel(new ImageIcon("images/搜索图标.png"));
        search.setBounds(1132, 68, 183, 100);
        cp.add(search);

        swi = new JLabel(new ImageIcon("images/off.png"));
        swi.setBounds(1120, 796, 183, 100);
        cp.add(swi);

        borrow = new JLabel(new ImageIcon("images/借阅.png"));
        borrow.setBounds(1458, 870, 400, 100);
        cp.add(borrow);


        keyQuery = new JTextField();
        keyQuery.setBounds(650, 98, 400, 40);
        beautifyTextField(keyQuery);
        cp.add(keyQuery);

        typeQuery = new JTextField();
        typeQuery.setBounds(620, 169, 150, 40);
        beautifyTextField(typeQuery);
        cp.add(typeQuery);

        publisherQuery = new JTextField();
        publisherQuery.setBounds(885, 169, 150, 40);
        beautifyTextField(publisherQuery);
        cp.add(publisherQuery);

        publicDateQuery = new JTextField();
        publicDateQuery.setBounds(1141, 169, 150, 40);
        beautifyTextField(publicDateQuery);
        cp.add(publicDateQuery);

        bookName = new JTextField(v.get(0).getBookName());
        bookName.setBounds(1470, 169, 300, 40);
        beautifyTextField(bookName);
        bookName.setEditable(false);
        cp.add(bookName);

        position = new JTextField(v.get(0).getPosition());
        position.setBounds(1550, 115, 300, 40);
        beautifyTextField(position);
        position.setEditable(false);
        cp.add(position);

        bookImageURL="book/EB1026.png";
        bookImage = new JLabel(new ImageIcon(bookImageURL));
        bookImage.setBounds(1420, 228, 400, 400);
        cp.add(bookImage);

        writer = new JTextField(v.get(0).getWriterName());
        beautifyTextField(writer);
        writer.setBounds(1550, 680, 300, 40);
        writer.setEditable(false);
        cp.add(writer);

        publisherName = new JTextField(v.get(0).getPublisher());
        beautifyTextField(publisherName);
        publisherName.setBounds(1550, 735, 300, 40);
        publisherName.setEditable(false);
        cp.add(publisherName);

        publicTime = new JTextField(v.get(0).getPublicDate().substring(0,7));
        beautifyTextField(publicTime);
        publicTime.setBounds(1550, 795, 300, 40);
        publicTime.setEditable(false);
        cp.add(publicTime);

        select1 = new JLabel(new ImageIcon("images/三角左.png"));
        select1.setBounds(770, 179, 20, 20);
        cp.add(select1);

        select2 = new JLabel(new ImageIcon("images/三角左.png"));
        select2.setBounds(1034, 179, 20, 20);
        cp.add(select2);

        select3 = new JLabel(new ImageIcon("images/三角左.png"));
        select3.setBounds(1290, 179, 20, 20);
        cp.add(select3);

        keyQueryLabel = new JLabel(new ImageIcon("images/关键字查找.png"));
        keyQueryLabel.setBounds(805,99,100,40);
        cp.add(keyQueryLabel);

        typeQueryLabel = new JLabel(new ImageIcon("images/类别查找.png"));
        typeQueryLabel.setBounds(643,169,100,40);
        cp.add(typeQueryLabel);

        publisherQueryLabel = new JLabel(new ImageIcon("images/出版社查找.png"));
        publisherQueryLabel.setBounds(911,169,100,40);
        cp.add(publisherQueryLabel);

        publicDateQueryLabel = new JLabel(new ImageIcon("images/出版时间查找.png"));
        publicDateQueryLabel.setBounds(1159,169,120,40);
        cp.add(publicDateQueryLabel);

        storedNumText = new JTextField("3");//初始化为第一本书的信息
        beautifyTextField(storedNumText);
        storedNumText.setBounds(710, 937, 40, 40);
        storedNumText.setEditable(false);
        cp.add(storedNumText);

        borrowedNumText = new JTextField("2");//初始化为第一本书的信息
        beautifyTextField(borrowedNumText);
        borrowedNumText.setBounds(1025, 937, 40, 40);
        borrowedNumText.setEditable(false);
        cp.add(borrowedNumText);

        leftNumText = new JTextField("1");//初始化为第一本书的信息
        beautifyTextField(leftNumText);
        leftNumText.setBounds(1285, 937, 40, 40);
        leftNumText.setEditable(false);
        cp.add(leftNumText);

        /*
        * "借阅"按钮鼠标移入移出和点击事件
        * */

        borrow.addMouseListener(new MouseListener() {
            @Override
            public void mouseClicked(MouseEvent e) {

            }

            @Override
            public void mousePressed(MouseEvent e) {
                borrow.setIcon(new ImageIcon("images/借阅.png"));
            }

            @Override
            public void mouseReleased(MouseEvent e) {
                borrow.setIcon(new ImageIcon("images/借阅1.png"));
            }

            @Override
            public void mouseEntered(MouseEvent e) {
                borrow.setIcon(new ImageIcon("images/借阅1.png"));
            }

            @Override
            public void mouseExited(MouseEvent e) {
                borrow.setIcon(new ImageIcon("images/借阅.png"));
            }
        });
        /*
         * "开关"鼠标移入移出和点击事件
         * */
        swi.addMouseListener(new MouseListener() {
            @Override
            public void mouseClicked(MouseEvent e) {

            }

            @Override
            public void mousePressed(MouseEvent e) {
                if ( ! swi_on){
                    swi.setIcon(new ImageIcon("images/on.png"));
                    swi_on = true;
                    if(keyQuery.getText().length()!=0){

                        tableModel = SQLOperation(SWI_ON);
                    }else{
                        tableModel = SQLOperation(SWI_ON);
                    }

                    table.setModel(tableModel);
                    beautyTable();//★★★★每一次都要美化表格，否则要出错★★★★

                }
                else {
                    swi.setIcon(new ImageIcon("images/off.png"));
                    swi_on = false;
                    tableModel = SQLOperation(SWI_OFF);
                    table.setModel(tableModel);
                    beautyTable();//★★★★每一次都要美化表格，否则要出错★★★★
                }
            }

            @Override
            public void mouseReleased(MouseEvent e) {

            }

            @Override
            public void mouseEntered(MouseEvent e) {

            }

            @Override
            public void mouseExited(MouseEvent e) {

            }
        });
        /*
         * "退出登录"点击事件
         * */
        exit.addMouseListener(new MouseListener() {
            @Override
            public void mouseClicked(MouseEvent e) {
                setVisible(false);
                Login login = new Login();
                login.setVisible(true);
            }

            @Override
            public void mousePressed(MouseEvent e) {
                exit.setIcon(new ImageIcon("images/退出登录.png"));
            }

            @Override
            public void mouseReleased(MouseEvent e) {
                exit.setIcon(new ImageIcon("images/退出登录1.png"));
            }

            @Override
            public void mouseEntered(MouseEvent e) {
                exit.setIcon(new ImageIcon("images/退出登录1.png"));
            }

            @Override
            public void mouseExited(MouseEvent e) {
                exit.setIcon(new ImageIcon("images/退出登录.png"));
            }
        });

        /*
         * "查询"点击事件
         * */
        search.addMouseListener(new MouseListener() {
            @Override
            public void mouseClicked(MouseEvent e) {

            }

            @Override
            public void mousePressed(MouseEvent e) {
                search.setIcon(new ImageIcon("images/搜索图标.png"));
                int num = keyQuery.getText().length();
                /*
                 * 让每个字都有效
                 * */
                String [] s = new String[num];
                for(int i=0;i<num;i++)//让每个字都有效
                    s[i] = "'%"+keyQuery.getText().substring(i,i+1)+"%'";//两边有单引号
                String SQL = SEARCH_BY_KEYVALUE;
                for(int i=0;i<num-1;i++)
                    SQL+= s[i]+"or BookName like ";
                //防止出现数组下标越界的情况
                try{
                    SQL+=s[num-1];
                    tableModel = SQLOperation(SQL);
                    table.setModel(tableModel);
                    //★★★★每一次都要美化表格，否则要出错★★★★
                    beautyTable();
                }catch (ArrayIndexOutOfBoundsException ae){
                    System.out.println("数组下标越界");
                }
            }

            @Override
            public void mouseReleased(MouseEvent e) {
                search.setIcon(new ImageIcon("images/搜索图标1.png"));

            }

            @Override
            public void mouseEntered(MouseEvent e) {
                search.setIcon(new ImageIcon("images/搜索图标1.png"));
            }

            @Override
            public void mouseExited(MouseEvent e) {
                search.setIcon(new ImageIcon("images/搜索图标.png"));
            }
        });
        /*
         * "关键字查找"获得焦点事件
         * */
        keyQuery.addFocusListener(new FocusListener() {
            @Override
            public void focusGained(FocusEvent e) {
                keyQueryLabel.setVisible(false);
            }

            @Override
            public void focusLost(FocusEvent e) {
                if(keyQuery.getText().length()==0)
                    keyQueryLabel.setVisible(true);
            }
        });

        /*
         * "种类查找"获得焦点事件
         * */
        typeQuery.addFocusListener(new FocusListener() {
            @Override
            public void focusGained(FocusEvent e) {
                select1.setIcon(new ImageIcon("images/三角下.png"));
                typeQueryLabel.setVisible(false);
                /*
                * 这里将会产生一个下拉菜单
                * */

            }

            @Override
            public void focusLost(FocusEvent e) {
                select1.setIcon(new ImageIcon("images/三角左.png"));
                if(typeQuery.getText().length()==0)
                    typeQueryLabel.setVisible(true);
                int num = typeQuery.getText().length();//获得种类查找的字符串长度
                String SQL = SEARCH_BY_TYPE+"'"+typeQuery.getText()+"'";
                if(num!=0){
                    tableModel = SQLOperation(SQL);
                    table.setModel(tableModel);
                    beautyTable();
                }
                /*
                 * 这里下拉菜单将会消失
                 * */

            }
        });
        /*
         * "出版社查找"获得焦点事件
         * */
        publisherQuery.addFocusListener(new FocusListener() {
            @Override
            public void focusGained(FocusEvent e) {
                select2.setIcon(new ImageIcon("images/三角下.png"));
                publisherQueryLabel.setVisible(false);
                /*
                 * 这里将会产生一个下拉菜单
                 * */
            }

            @Override
            public void focusLost(FocusEvent e) {
                select2.setIcon(new ImageIcon("images/三角左.png"));
                if(publisherQuery.getText().length()==0)
                    publisherQueryLabel.setVisible(true);
                int num = publisherQuery.getText().length();//获得出版社查找的字符串长度
                String SQL = SEARCH_BY_PUBLISHER+"'%"+publisherQuery.getText()+"%'";
                if(num!=0){
                    tableModel = SQLOperation(SQL);
                    table.setModel(tableModel);
                    beautyTable();
                }
                /*
                 * 这里下拉菜单将会消失
                 * */

            }
        });
        /*
         * "出版日期查找"获得焦点事件
         * */
        publicDateQuery.addFocusListener(new FocusListener() {
            @Override
            public void focusGained(FocusEvent e) {
                select3.setIcon(new ImageIcon("images/三角下.png"));
                publicDateQueryLabel.setVisible(false);
                /*
                 * 这里将会产生一个下拉菜单
                 * */
            }

            @Override
            public void focusLost(FocusEvent e) {
                select3.setIcon(new ImageIcon("images/三角左.png"));
                if(publicDateQuery.getText().length()==0)
                    publicDateQueryLabel.setVisible(true);
                int num = publicDateQuery.getText().length();//获得出版日期查找的字符串长度
                String SQL = SEARCH_BY_PUBLICDATE+"'"+publicDateQuery.getText()+"'";
                if(num!=0){
                    tableModel = SQLOperation(SQL);
                    table.setModel(tableModel);
                    beautyTable();
                }
                /*
                 * 这里下拉菜单将会消失
                 * */

            }
        });
        /*
         * "列表"鼠标移入移出和点击事件★★★
         * */
        table.addMouseListener(new MouseListener() {
            @Override
            public void mouseClicked(MouseEvent e) {

            }

            @Override
            public void mousePressed(MouseEvent e) {
                if(table.getValueAt(table.getSelectedRow(),0)!=null){
                    bookImageURL = v.get(table.getSelectedRow()).getBookImage();
                    //更新视图
                    bookImage.setIcon(new ImageIcon(bookImageURL));
                    position.setText(v.get(table.getSelectedRow()).getPosition());
                    bookName.setText(v.get(table.getSelectedRow()).getBookName());
                    writer.setText(v.get(table.getSelectedRow()).getWriterName());
                    publisherName.setText(v.get(table.getSelectedRow()).getPublisher());
                    publicTime.setText(v.get(table.getSelectedRow()).getPublicDate().substring(0,7));
                    /*
                    * 通过SQL语句来获得书本的库存，借出量，存储量
                    * */
                    SQLSetNum(SEARCH_ALL_NUMBER+"'"+v.get(table.getSelectedRow()).getBookName()+"'"
                    ,SEARCH_BORROW_NUMBER+"'"+v.get(table.getSelectedRow()).getBookName()+"'"
                    , SEARCH_LEFT_NUMBER+"'"+v.get(table.getSelectedRow()).getBookName()+"'");
                    storedNumText.setText(storedNum);
                    borrowedNumText.setText(borrowedNum);
                    leftNumText.setText(leftNum);
                }
            }

            @Override
            public void mouseReleased(MouseEvent e) {

            }

            @Override
            public void mouseEntered(MouseEvent e) {

            }

            @Override
            public void mouseExited(MouseEvent e) {

            }
        });

        /*
        * 键盘回车事件
        * */
        keyQuery.addKeyListener(new KeyListener() {
            @Override
            public void keyTyped(KeyEvent e) {
                if(e.getKeyChar() == KeyEvent.VK_ENTER){
                    int num = keyQuery.getText().length();
                    /*
                     * 让每个字都有效
                     * */
                    String [] s = new String[num];
                    for(int i=0;i<num;i++)//让每个字都有效
                        s[i] = "'%"+keyQuery.getText().substring(i,i+1)+"%'";//两边有单引号
                    String SQL = SEARCH_BY_KEYVALUE;
                    for(int i=0;i<num-1;i++)
                        SQL+= s[i]+"or BookName like ";
                    //防止出现数组下标越界的情况
                    try{
                        SQL+=s[num-1];
                        tableModel = SQLOperation(SQL);
                        table.setModel(tableModel);
                        //★★★★每一次都要美化表格，否则要出错★★★★
                        beautyTable();
                    }catch (ArrayIndexOutOfBoundsException ae){
                        System.out.println("数组下标越界");
                    }

                }
            }

            @Override
            public void keyPressed(KeyEvent e) {

            }

            @Override
            public void keyReleased(KeyEvent e) {

            }
        });


    }

    /*
     * 文本框美化方法复用
     * */
    private static void beautifyTextField(JTextField jTextfield){
        jTextfield.setOpaque(false);
        jTextfield.setHorizontalAlignment(JTextField.CENTER);
        jTextfield.setFont(new Font("黑体", Font.PLAIN, 22));
        jTextfield.setBorder(new LineBorder(null,0));
    }
    /*
    * 返回一个表格对象,复用
    * */
    private void beautyTable(){

        DefaultTableCellRenderer r = new DefaultTableCellRenderer();
        r.setHorizontalAlignment(JLabel.CENTER);
        table.setDefaultRenderer(Object.class, r);//设置居中方式
        table.getTableHeader().setReorderingAllowed(false);
        table.getTableHeader().setResizingAllowed(false);
        table.getTableHeader().setVisible(false);
        table.setRowHeight(40);
        table.setShowGrid(false);//不显示网格线
        table.getColumnModel().getColumn(0).setPreferredWidth(300);
        table.setFont(new Font("黑体", Font.PLAIN, 22));
        table.setPreferredScrollableViewportSize(new Dimension(500, 250));
    }

    /*
    * ★★★★★★★★执行SQL语句的方法来返回一个新的表格模型★★★★★★★
    * 复用
    * */
    private  MyTableModel SQLOperation(String SQL){

        MyTableModel tableModel1 = new MyTableModel();
        DB dbcon;
        dbcon = new DB();
        try {
            ResultSet rs = dbcon.executeQuery(SQL);
            ResultSetMetaData rsmd = rs.getMetaData();
            for(int i=1;i<=3;i++)
                tableModel1.addColumn(rsmd.getColumnName(i));
            /*
            * 这句话非常重要，不写这句话，那么每次更新，原来的数据都不会消除
            * */
            v = new ArrayList<>();
            while(rs.next()){
                bookEntity book = new bookEntity();
                book.setBookName(rs.getString("BookName"));
                book.setBookNo(rs.getString("BookNo"));
                book.setIsBorrowed(rs.getString("IsBorrowed"));
                book.setPosition(rs.getString("Position"));
                book.setWriterName(rs.getString("WriterName"));
                book.setBookImage(rs.getString("BookImage"));
                book.setPublisher(rs.getString("Publisher"));
                book.setPublicDate(rs.getString("PublicDate"));
                book.setBookType(rs.getString("BookType"));
                v.add(book);
            }
            rs.close();
            for(int i=0;i<v.size();i++){
                tableModel1.addRow(new Object []{v.get(i).getBookName(),v.get(i).getBookNo(),v.get(i).getIsBorrowed()});
            }
            dbcon.closeConn();

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return tableModel1;
    }

    /*
     * ★★★★★★★★执行SQL语句的方法来返回一个数据对象★★★★★★★
     * ★★一次连接多次查询★★
     * 复用
     * */
    private void SQLSetNum(String SQL1,String SQL2,String SQL3){
        DB dbcon;
        dbcon = new DB();
        try {
            ResultSet rs1 = dbcon.executeQuery(SQL1);
            ResultSet rs2 = dbcon.executeQuery(SQL2);
            ResultSet rs3 = dbcon.executeQuery(SQL3);
            rs1.last();
            rs2.last();
            rs3.last();
            this.storedNum = rs1.getString(1);//获得结果集
            this.borrowedNum = rs2.getString(1);
            this.leftNum = rs3.getString(1);
            rs1.close();
            rs2.close();
            rs3.close();
            dbcon.closeConn();

        } catch (SQLException e) {
            e.printStackTrace();
        }

    }


    public static void main(String[] args) {
        ReaderList list =new ReaderList();
        list.setVisible(true);
    }
}
